<?php

class salesreceiptsreport
{
	public function salesreceiptsreport($params)
	{
		foreach($params as $key => $value) {
			$this->{$key}=$value;
		}
		$this->getSpecialRoomIdList();
	}
	
	public function getSpecialFloorID() 
	{
		$sql = " select settings_value from settings where settings_name = 'SPECIALFLOORID' ";
		$res = mysql_query($sql) or die($sql);
		
		if(mysql_num_rows($res)){
			$row = mysql_fetch_row($res);
			$this->intSpecialFloorId = $row[0];
		} 
	}
	
	public function getSpecialRoomIdList()
	{
		//echo "getSpecialRoomIdList<br>";
		$this->getSpecialFloorId();
		$sql = " select room_id from rooms where floor_id=".$this->intSpecialFloorId;
		
		$res = mysql_query($sql) or die($sql);
		while(list($id)=mysql_fetch_row($res)) {
			$this->arrSpecialRooms[]=$id;
		}
		$this->exceptlist = implode(',',$this->arrSpecialRooms);
	}
	
	public function getshift($date) {
		if(!$date)$date=date("Y-m-d H:i:s");
		list($d, $t) = explode(" ", $date);
		list($h, $m, $s) = explode(":", $t);
		$sql = "select shift_id from shifts where $h between shift_start and shift_end";
		$res = mysql_query($sql) or die(mysql_error() .$sql);
		list($shift)=mysql_fetch_row($res);
		
		if($h==16||$h==15)
		{
			$this->shiftnum=3;
			return $shift = "3rd";
		}
		elseif($h==8||$h==7)
		{
			$this->shiftnum=2;
			return $shift = "2nd";
		}
		$this->shiftnum=1;
	return  "1st";
	}
	
	public function getLatestShifts($shiftid)
	{
		$sql = "SELECT `shift-transaction_id`,datetime,user_id  FROM `shift-transactions` where shift = 'start' order by datetime desc ";
		$res = mysql_query($sql);
		$ret = "<select name='rblshifts' id='rblshifts' onchange='myform.submit();'>";
		$ret .= "<option value=''>&nbsp;</option>";
		while(list($shift_transaction_id,$datetime,$userid)=mysql_fetch_row($res))
		{
			if($shiftid == $shift_transaction_id)
			{
				$select = "selected";
			}else
			{
				$select = " ";
			}
			$__sql = "SELECT user_id  FROM `shift-transactions` where shift = 'end' 
			and `datetime` > '$datetime'
			order by datetime asc 
			limit 0,1";
			$__res = mysql_query($__sql);
			list($userid)=mysql_fetch_row($__res);
			$_sql = "select fullname from users where user_id = '$userid'";
			$_res = mysql_query($_sql);
			list($username)=mysql_fetch_row($_res);
			$ret .= "<option value='$shift_transaction_id' $select>$datetime - $username - ".$this->getshift($datetime)."</option>";
		}
		$ret .= "</select>";
		return $ret;
	}
	
	public function getCashOnHand()
	{
		$sql = "select amount,datetime from cash_on_hand where datetime >='$this->start' and datetime <= '$this->end' ";
		$res = mysql_query($sql);
		$row = mysql_fetch_row($res);
		$this->cashOnHand=$row[0];
		return '<h3>Declared Cash On Hand ['. $row[1] . ']  -  Php ' . number_format($this->cashOnHand,2) .'</h3>';
	}
	
	public function getSafeKeep()
	{

		$sql = "select a.safekeep_date, a.amount, b.fullname as 'cashier','none' as 'oic'
				from safekeep a, users b
				where a.cashier_id=b.user_id
				and a.safekeep_date >='$this->start' and a.safekeep_date <='$this->end'
				order by a.safekeep_date
			";

		$res = mysql_query($sql) or die($sql);
		if(mysql_num_rows($res)) {
			$ret = '<h3>Safekeeping Records</h3>';
			$ret .= '<table class=report>';
			$ret.='<tr><th>Date</th><th>Amount</th><th>Cashier</th><th>OIC</th></tr>';
			while( list($date,$amount,$cashier,$oic)=mysql_fetch_row($res) ) {
				$ret.='<tr>';
				$ret.="<td>$date</td>";
				$ret.="<td>$amount</td>";
				$ret.="<td>$cashier</td>";
				$ret.="<td>$oic</td>";
				$ret.='</tr>';
				$total+=$amount;
			}
			$ret.="<tr><th></th><th>$total</th><th></th><th></th></tr>";
			$ret .= '</table>';
		}
		return $ret;
	}
	
	public function getSalesreceipts()
	{

		$sql = "select a.salesreceipt_id, a.receipt_date, d.door_name, 
				if(a.tendertype<>'Card', a.amount, '') as 'Cash', if(a.tendertype='Card', a.amount, '') as 'Card' , 
				a.tendertype, b.fullname as 'cashier',a.occupancy_id 
				from salesreceipts a, users b, occupancy c, rooms d
				where a.update_by=b.user_id
				and a.occupancy_id=c.occupancy_id
				and c.room_id=d.room_id
				and a.receipt_date >='$this->start' and a.receipt_date <='$this->end'
				order by a.receipt_date
			";
		$res = mysql_query($sql) or die($sql);
		if(mysql_num_rows($res)) {
			$ret = '<h3>Sales Receipts Records</h3>';
			$ret .= '<table class=report>';
			$ret.='<tr><th>Date</th><th>Room No.</th><th>Cash</th><th>Card</th><th>TenderType</th><th>Cashier</th></tr>';
			while( list($sid,$date,$door,$cash,$card,$tender,$cashier,$occupancy)=mysql_fetch_row($res) ) {
				$ret.='<tr>';
				$ret.="<td>$date</td>";
				$ret.="<td class='numeric'>$door</td>";
				$ret.="<td class='numeric'>$cash</td>";
				$ret.="<td class='numeric'>$card</td>";
				$ret.="<td>$tender</td>";
				$ret.="<td>$cashier</td>";
				$ret.="<td class='debug'>$sid</td>";
				$ret.='<td class="debug"><a href="occupancydetails.php?occ='.$occupancy.'" target="_blank">'.$occupancy.'</a></td>';
				$ret.='</tr>';
				$this->total['cash']+=$cash;
				$this->total['card']+=$card;
			}
			$ret .= "<tr><th>&nbsp;</th><th>&nbsp;</th>
			<th class='numeric'>{$this->total['cash']}</th>
			<th class='numeric'>{$this->total['card']}</th>
			<th>&nbsp;</th><th>&nbsp;</th></tr>";
			$ret .= '</table>';
		}
		return $ret;
	}
	
	public function getCumulativeTransactions()
	{

		$sql = "select a.cc_id, a.cc_date, a.transaction_amount,  a.current_amount, a.transaction_type,  b.fullname as 'cashier' 
				from current_cash a, users b
				where a.update_by=b.user_id
				and a.cc_date >='$this->start' and a.cc_date <='$this->end'
				order by a.cc_date
			";
		$res = mysql_query($sql) or die($sql);
		if(mysql_num_rows($res)) {
			$ret = '<h3>Transactions</h3>';
			$ret .= '<table class=report>';
			$ret.='<tr><th>Date</th><th>Transaction Amount</th><th>Running Amount</th><th>Transaction Type</th><th>Cashier</th></tr>';
			while( list($sid,$date,$tamt,$camt,$tender,$cashier)=mysql_fetch_row($res) ) {
				$ret.='<tr>';
				$ret.="<td>$date</td>";
				$ret.="<td class='numeric'>$tamt</td>";
				$ret.="<td class='numeric'>$camt</td>";
				$ret.="<td>$tender</td>";
				$ret.="<td>$cashier</td>";
				$ret.="<td>$sid</td>";
				$ret.='</tr>';
				if($tender=='In')
				$this->total['tamt']+=$tamt;

			}
			$ret .= "<tr><th>&nbsp;</th>
			<th class='numeric'>{$this->total['tamt']}</th>
			<th>&nbsp;</th>
			<th class='numeric'>&nbsp;</th>
			<th>&nbsp;</th><th>&nbsp;</th></tr>";
			$ret .= '</table>';
		}
		return $ret;
	}
	
	public function getInTransitFromNewCheckin()
	{
		$sql = "select distinct b.door_name as 'Room No.',a.actual_checkin as 'Checkin',
			a.actual_checkout as 'Checkout', 
			if(b.site_id=1,'Walkup','Hotel') as 'Site', c.room_type_name as 'Room Type',
			d.rate_name as 'Rate', 
			b.room_id , c.room_type_id
			,d.rate_id,a.occupancy_id
		from occupancy a, rooms b, room_types c, rates d 
		where 
		(
			(a.actual_checkin >= '$this->start' 
			and			
			a.actual_checkin <= '$this->end')
			and 
			a.actual_checkout = '0000-00-00 00:00:00'
		)
		
		and a.room_id = b.room_id
		and b.room_type_id=c.room_type_id
		and a.rate_id=d.rate_id
		
		";
		echo $sql;
		$res = mysql_query($sql) or die(mysql_error() . $sql);
		
		$numrows =mysql_num_rows($res); 
		if($numrows) {
			$numfields = mysql_num_fields($res);
			$retval="
			<strong>InTransit Sales</strong>: New checkin w/in shift
			<br/>$numrows records found.<br/><table class='report'>";
			$retval.="<tr>";
			for($i=0; $i < $numfields; $i++) {
				$field=mysql_field_name($res,$i);
				$retval.="<th>$field</th>";
			}
			$retval.="<th>Food</th>";
			$retval.="<th>Beer</th>";
			$retval.="<th>Total</th>";
			$retval.="</tr>";
			$grandtotal=0;
			while($row=mysql_fetch_row($res)) {
				$retval.="<tr>";
				foreach($row as $rowvalue) {
					$retval.="<td>$rowvalue</td>";
				}
				$food=$this->getFoodSales($row[9],'food');
				$beer=$this->getFoodSales($row[9],'beer');
				$total = $food + $beer + $row[10];
				$rostotal += $row[10];
				$foodtotal+=$food;
				$beertotal+=$beer;
				$grandtotal +=$total;
				$retval.="<td>$food</td>";
				$retval.="<td>$beer</td>";
				$retval.="<td>$total</td>";
				$retval.="</tr>";
			}
			$retval.="<tr><td colspan='9'>&nbsp;</td><td>Grand Total:</td>
			<td>$rostotal</td>
			<td>$foodtotal</td>
			<td>$beertotal</td>
			<td>$grandtotal</td></tr>";
			$retval.="</table>";
		}
		$this->grandtotals['InTransit Sales: New Checkin within Shift']=$grandtotal;
		return $retval;

	}
	
	public function getSalesForRechit()
	{
		$sql = "select distinct b.door_name as 'Room No.',a.actual_checkin as 'Checkin',
				a.actual_checkout as 'Checkout', 
				if(b.site_id=1,'Walkup','Hotel') as 'Site', c.room_type_name as 'Room Type',
				d.rate_name as 'Rate', 
				b.room_id , c.room_type_id
				,d.rate_id,a.occupancy_id
			from occupancy a, rooms b, room_types c, rates d 
			where 
			(
				(a.actual_checkout >= '$this->end' or a.actual_checkout='0000-00-00 00:00:00')
			)
			
			and a.room_id = b.room_id
			and b.room_type_id=c.room_type_id
			and a.rate_id=d.rate_id
			and a.room_id not in ({$this->exceptlist})
			";
			
		$res = mysql_query($sql) or die(mysql_error() . $sql);
		
		$numrows =mysql_num_rows($res); 
		if($numrows) {
			$numfields = mysql_num_fields($res);
			$retval="
			<strong>In Transit</strong>: Cash Accepted for Rechit Later
			<br/>$numrows records found.<br/><table class='report'>";
			$retval.="<tr>";
			for($i=0; $i < $numfields; $i++) {
				$field=mysql_field_name($res,$i);
				$retval.="<th>$field</th>";
			}
			$retval.="<th>Ros</th>";
			$retval.="<th>Food</th>";
			$retval.="<th>Beer</th>";
			$retval.="<th>Total</th>";
			$retval.="</tr>";
			$grandtotal=0;
			while($row=mysql_fetch_row($res)) {
				$retval.="<tr>";
				foreach($row as $rowvalue) {
					$retval.="<td>$rowvalue</td>";
				}
				$ros=$this->getRoomSales($row[9]);
				$food=$this->getFoodSales($row[9],'food');
				$beer=$this->getFoodSales($row[9],'beer');
				$total = $food + $beer + $row[10];
				$retval.="<td>$ros</td>";
				$retval.="<td>$food</td>";
				$retval.="<td>$beer</td>";
				$retval.="<td>$total</td>";
				$rostotal += $row[10];
				$foodtotal+=$food;
				$beertotal+=$beer;
				$grandtotal+=$total;
				$retval.="</tr>";
			}
			$retval.="<tr><td colspan='9'>&nbsp;</td><td>Grand Total:</td>
			<td>$rostotal</td>
			<td>$foodtotal</td>
			<td>$beertotal</td>
			<td>$grandtotal</td></tr>";
			$retval.="</table>";
		}
		$this->grandtotals['InTransit Sales: Payment Received for Rechit Later']=$grandtotal;
		return $retval;
	}
	
	public function getPaymentOnCheckout()
	{
		$sql = "select distinct b.door_name as 'Room No.',a.actual_checkin as 'Checkin',
				a.actual_checkout as 'Checkout', 
				if(b.site_id=1,'Walkup','Hotel') as 'Site', c.room_type_name as 'Room Type',
				d.rate_name as 'Rate', 
				b.room_id , c.room_type_id
				,d.rate_id,a.occupancy_id
			from occupancy a, rooms b, room_types c, rates d 
			where 
			(
				a.actual_checkout >= '$this->start' and a.actual_checkout <= '$this->end' 
			)
			
			and a.room_id = b.room_id
			and b.room_type_id=c.room_type_id
			and a.rate_id=d.rate_id
			
			";
		$res = mysql_query($sql) or die(mysql_error() . $sql);
		
		$numrows =mysql_num_rows($res); 
		if($numrows) {
			$numfields = mysql_num_fields($res);
			$retval="
			<strong>Checkout Sales</strong>: Payment Received Upon Checkout
			<br/>$numrows records found.<br/><table class='report'>";
			$retval.="<tr>";
			for($i=0; $i < $numfields; $i++) {
				$field=mysql_field_name($res,$i);
				$retval.="<th>$field</th>";
			}
			$retval.="<th>ROS</th>";
			$retval.="<th>Food</th>";
			$retval.="<th>Beer</th>";
			$retval.="<th>Total</th>";
			$retval.="</tr>";
			$grandtotal=0;
			while($row=mysql_fetch_row($res)) {
				$retval.="<tr>";
				foreach($row as $rowvalue) {
					$retval.="<td>$rowvalue</td>";
				}
				$ros =$this->getRoomSales($row[9]);
				$food=$this->getFoodSales($row[9],'food');
				$beer=$this->getFoodSales($row[9],'beer');
				$total = $food + $beer + $ros;
				$retval.="<td>$ros</td>";
				$retval.="<td>$food</td>";
				$retval.="<td>$beer</td>";
				$retval.="<td>$total</td>";
				$rostotal += $ros;
				$foodtotal+=$food;
				$beertotal+=$beer;
				$grandtotal+=$total;
				$retval.="</tr>";
			}
			$retval.="<tr><td colspan='9'>&nbsp;</td><td>Grand Total:</td>
			<td>$rostotal</td>
			<td>$foodtotal</td>
			<td>$beertotal</td>
			<td>$grandtotal</td></tr>";
			$retval.="</table>";
		}
		$this->grandtotals['Checkout Sales: Payment Received Upon Checkout']=$grandtotal;
		return $retval;
	}
	
	public function getClosedSales()
	{
		$sql = "select distinct b.door_name as 'Room No.',a.actual_checkin as 'Checkin',
				a.actual_checkout as 'Checkout', 
				if(b.site_id=1,'Walkup','Hotel') as 'Site', c.room_type_name as 'Room Type',
				d.rate_name as 'Rate', 
				b.room_id , c.room_type_id
				,d.rate_id,a.occupancy_id, sum(e.unit_cost*e.qty) as 'ROS'
			from occupancy a, rooms b, room_types c, rates d , room_sales e
			where 
			(
				a.actual_checkout >= '$this->start' and a.actual_checkout <= '$this->end'
				and 
				a.actual_checkin >= '$this->start' and a.actual_checkin <= '$this->end'
			)
			and a.occupancy_id=e.occupancy_id
			and a.room_id = b.room_id
			and b.room_type_id=c.room_type_id
			and a.rate_id=d.rate_id
			and e.status='Paid'
			group by e.occupancy_id
			";
		$res = mysql_query($sql) or die(mysql_error() . $sql);
		
		$numrows =mysql_num_rows($res); 
		if($numrows) {
			$numfields = mysql_num_fields($res);
			$retval="
			<strong>Actual Sales</strong>: Checkin/Checkout w/in shift
			<br/>$numrows records found.<br/><table class='report'>";
			$retval.="<tr>";
			for($i=0; $i < $numfields; $i++) {
				$field=mysql_field_name($res,$i);
				$retval.="<th>$field</th>";
			}
			$retval.="<th>Food</th>";
			$retval.="<th>Beer</th>";
			$retval.="<th>Total</th>";
			$retval.="</tr>";
			$grandtotal=0;
			while($row=mysql_fetch_row($res)) {
				$retval.="<tr>";
				foreach($row as $rowvalue) {
					$retval.="<td>$rowvalue</td>";
				}
				$food=$this->getFoodSales($row[9],'food');
				$beer=$this->getFoodSales($row[9],'beer');
				$total = $food + $beer + $row[10];
				$retval.="<td>$food</td>";
				$retval.="<td>$beer</td>";
				$retval.="<td>$total</td>";
				$rostotal += $row[10];
				$foodtotal +=$food;
				$beertotal +=$beer;
				$grandtotal+=$total;
				$retval.="</tr>";
			}
			$retval.="<tr><td colspan='9'>&nbsp;</td><td>Grand Total:</td>
			<td>$rostotal</td>
			<td>$foodtotal</td>
			<td>$beertotal</td>
			<td>$grandtotal</td></tr>";
			$retval.="</table>";
		}
		$this->grandtotals['Actual Sales: Checkin/Checkout Within Shift']=$grandtotal;
		return $retval;
	}
	
	public function getRoomSales($occ)
	{
		
		$sql = "select sum(unit_cost*qty) from 
				room_sales 
				where 
				occupancy_id='$occ' 
				and update_date >='$this->start' and update_date <='$this->end'
			";
		$res = mysql_query($sql) or die(mysql_error(). $sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}
	
	public function getFoodSales($occ,$type='food')
	{
		$not = ($type=='food') ? 'not' : '';
		$sql = "select sum(unit_cost*qty) from 
				fnb_sales 
				where 
				category_id $not in (17,21)
				and 
				occupancy_id='$occ'
				and update_date >='$this->start' and update_date <='$this->end'
			";
		$res = mysql_query($sql) or die(mysql_error(). $sql);
		$row = mysql_fetch_row($res);
		return $row[0];
	}
	
	public function summary()
	{
		$sql = "select sum(unit_cost*qty) from room_sales
				where update_date >='$this->start' and update_date <='$this->end'
				and status='Paid'
			";
		$res = mysql_query($sql);
		list($roomsales)=mysql_fetch_row($res);
		
		$sql = "select sum(unit_cost*qty) from fnb_sales
				where update_date >='$this->start' and update_date <='$this->end'
				and status='Paid'  and category_id not in (17,21)
			";
		$res = mysql_query($sql);
		list($foodsales)=mysql_fetch_row($res);
		
		$sql = "select sum(unit_cost*qty) from fnb_sales
				where update_date >='$this->start' and update_date <='$this->end'
				and status='Paid' and category_id in (17,21)
			";
		$res = mysql_query($sql);
		list($beersales)=mysql_fetch_row($res);
		
		$retval="<table class='report'>";
		$total=0;
		
		foreach($this->grandtotals as $key=>$value) {
			$retval.="<tr><td>$key</td><th>$value</th></tr>";
			$total += $value;
		}
		
		$retval.="<tr><th>Total Sales This Shift</th><th>$total</th></tr>";
		$retval.="</table>";
		$retval.="<hr /><h3>All Sales (Cash + Card)</h3>";
		$retval.="<table class='report'>";
		$retval.="<tr><td>Room Sales</td><th>$roomsales</th></tr>";
		$retval.="<tr><td>FnB Sales</td><th>$foodsales</th></tr>";
		$retval.="<tr><td>Beer Sales</td><th>$beersales</th></tr>";
		$totalsales = $roomsales + $foodsales + $beersales;
		$retval.="<tr><th>Total Sales (Cash + Card)</th><th>$totalsales</th></tr>";
		$retval.="</table>";
		return $retval;
	}
	
}